package com.elitel.frame.base.component.exportprojectdata;

import java.io.*;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import com.elitel.frame.main.entity.DataSourceType;
import com.elitel.frame.main.entity.vo.KeyValueModelVoSqlite;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.core.io.FileSystemResource;
import org.springframework.core.io.InputStreamResource;
import org.springframework.core.io.support.EncodedResource;
import org.springframework.jdbc.datasource.init.ScriptUtils;
import org.springframework.stereotype.Component;
import com.elitel.frame.main.dao.ext.ColumnMapper;
import com.elitel.frame.main.dao.ext.ExtSysProjectMapper;
import com.elitel.frame.main.entity.vo.KeyValueModelVo;

/**
 * show 项目导出数据类
 *
 * @author guoyanfei
 * @date 2017/12/11
 */
@Component("exportProjectData")
public class ExportProjectData {
    @Autowired
    private ExtSysProjectMapper extSysProjectMapper;
    @Autowired
    private ColumnMapper columnMapper;
    @Autowired
    private DataBaseConnInfo dataBaseConnInfo;
    @Autowired
    private Environment environment;

    /**
     * show 通过项目code导出数据
     *
     * @param pcode(项目code)
     * @param progressSingleton
     * @param url(写入文件的路径)
     * @author guoyanfei
     * @date 2017/12/11
     */
    public String exportProjectDataBypcode(String pcode, ProgressSingleton progressSingleton, String url) {
        Date currentTime = new Date();
        SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmss");
        String dateString = formatter.format(currentTime);
        String fileName = pcode + dateString + ".txt";
        //sql文拼接串
        String sqltxt = "";
        //表字段,以','分割
        String fields = "";
        //存放服务信息,Key-服务CODE,Value-服务类型
        List<KeyValueModelVo> serviceKeyValue = new ArrayList<KeyValueModelVo>();
        //存放dbkey
        List<String> dbkeylist = new ArrayList<String>();
        //存放dtConfigId
        List<String> dtconfigidlist = new ArrayList<String>();
        //导出信息
        StringBuffer messagetxt = new StringBuffer();
        messagetxt.append("正在导出...<br/>");

        //1、导出项目表-SYS_PROJECT
        fields = getTableField("SYS_PROJECT");
        sqltxt = "select " + fields + " from SYS_PROJECT where PCODE='" + pcode + "'";
        List<Map<String, Object>> sysProjectmaps = columnMapper.executequerySql(sqltxt);
        if (sysProjectmaps != null && sysProjectmaps.size() > 0) {
            boolean result = exportDocument(sysProjectmaps, "SYS_PROJECT", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "4");
                messagetxt.append("项目表(SYS_PROJECT)导出完成,共" + sysProjectmaps.size() + "条记录,正在导出项目服务关联表(SYS_PRJSERVICE)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("项目表(SYS_PROJECT)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }
        }

        //2、导出项目服务关联表-SYS_PRJSERVICE
        fields = getTableField("SYS_PRJSERVICE");
        sqltxt = "select " + fields + " from SYS_PRJSERVICE where PCODE='" + pcode + "' ";
        List<Map<String, Object>> sysPrjservicemaps = columnMapper.executequerySql(sqltxt);
        if (sysPrjservicemaps != null && sysPrjservicemaps.size() > 0) {
            boolean result = exportDocument(sysPrjservicemaps, "SYS_PRJSERVICE", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "9");
                messagetxt.append("服务关联表(SYS_PRJSERVICE)导出完成,共" + sysPrjservicemaps.size() + "条记录,正在导出项目非结构化文档配置表(SYS_DOCUMENT)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("服务关联表(SYS_PRJSERVICE)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }

            //保存服务信息以便下面导出使用
            for (Map<String, Object> map : sysPrjservicemaps) {
                KeyValueModelVo keyValueModelVo = new KeyValueModelVo();
                keyValueModelVo.setKey(map.get("SERVICECODE").toString());
                keyValueModelVo.setValue(map.get("SERVICETYPE").toString());
                serviceKeyValue.add(keyValueModelVo);
            }
        }

        //2-1、导出项目非结构化文档管理表-SYS_DOCUMENT
        fields = getTableField("SYS_DOCUMENT");
        sqltxt = "select " + fields + " from SYS_DOCUMENT where GUID in (";
        if (serviceKeyValue != null && serviceKeyValue.size() > 0) {
            String keyvalue = "";
            for (KeyValueModelVo kv : serviceKeyValue) {
                if (kv.getValue().equals("docSearchServer")) {
                    if (keyvalue == null || keyvalue.equals("")) {
                        keyvalue += "'" + kv.getKey() + "'";
                    } else {
                        keyvalue += ",'" + kv.getKey() + "'";
                    }
                }
            }
            if (keyvalue == null || keyvalue.equals("")) {
                keyvalue = "''";
            }
            sqltxt += keyvalue;
        }
        sqltxt += ")";
        List<Map<String, Object>> sysDocumentmaps = columnMapper.executequerySql(sqltxt);
        if (sysDocumentmaps != null && sysDocumentmaps.size() > 0) {
            boolean result = exportDocument(sysDocumentmaps, "SYS_DOCUMENT", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "11");
                messagetxt.append("项目非结构化文档配置表(SYS_DOCUMENT)导出完成,共" + sysDocumentmaps.size() + "条记录,正在导出项目单表查询服务表(cfg_extend)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("项目非结构化文档配置表(SYS_DOCUMENT)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }
        }

        //3、导出单表查询服务表-CFG_EXTEND
        fields = getTableField("CFG_EXTEND");
        sqltxt = "select " + fields + " from CFG_EXTEND where GUID in(";
        if (serviceKeyValue != null && serviceKeyValue.size() > 0) {
            String keyvalue = "";
            for (KeyValueModelVo kv : serviceKeyValue) {
                if (kv.getValue().equals("singleTableServer")) {
                    if (keyvalue == null || keyvalue.equals("")) {
                        keyvalue += "'" + kv.getKey() + "'";
                    } else {
                        keyvalue += ",'" + kv.getKey() + "'";
                    }
                }
            }
            if (keyvalue == null || keyvalue.equals("")) {
                keyvalue = "''";
            }
            sqltxt += keyvalue;
        }
        sqltxt += ")";
        List<Map<String, Object>> cfgextendaps = columnMapper.executequerySql(sqltxt);
        if (cfgextendaps != null && cfgextendaps.size() > 0) {
            boolean result = exportDocument(cfgextendaps, "CFG_EXTEND", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "13");
                messagetxt.append("单表查询服务表(cfg_extend)导出完成,共" + cfgextendaps.size() + "条记录,正在导出项目主从表查询服务表(CFG_EXTMASTER)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("单表查询服务表(cfg_extend)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }

            //存放dbkey和dtconfigid
            for (Map<String, Object> map : cfgextendaps) {
                dbkeylist.add(map.get("DB_KEY").toString());
                dtconfigidlist.add(map.get("DT_CONFIG_ID").toString());
            }
        }

        //4、导出主从表查询服务表-CFG_EXTMASTER
        fields = getTableField("CFG_EXTMASTER");
        sqltxt = "select " + fields + " from CFG_EXTMASTER where GUID in(";
        sqltxt += getServiceID(serviceKeyValue, "masterConfigServer");
        List<Map<String, Object>> cfgextmastermaps = columnMapper.executequerySql(sqltxt);
        if (cfgextmastermaps != null && cfgextmastermaps.size() > 0) {
            boolean result = exportDocument(cfgextmastermaps, "CFG_EXTMASTER", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "18");
                messagetxt.append("主从表查询服务表(CFG_EXTMASTER)导出完成,共" + cfgextmastermaps.size() + "条记录,正在导出项目主从表中从表(BASE_MASTERDETAIL)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("主从表查询服务表(CFG_EXTMASTER)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }

            //存放dbkey和dtconfigid
            for (Map<String, Object> map : cfgextmastermaps) {
                dbkeylist.add(map.get("DB_KEY").toString());
                dtconfigidlist.add(map.get("DT_CONFIG_ID").toString());
            }
        }

        //4-1、导出主从表服务中从表信息-BASE_MASTERDETAIL
        fields = getTableField("BASE_MASTERDETAIL");
        sqltxt = "select " + fields + " from BASE_MASTERDETAIL where MGUID in(";
        sqltxt += getServiceID(serviceKeyValue, "masterConfigServer");
        List<Map<String, Object>> basemasterdetailmaps = columnMapper.executequerySql(sqltxt);
        if (basemasterdetailmaps != null && basemasterdetailmaps.size() > 0) {
            boolean result = exportDocument(basemasterdetailmaps, "BASE_MASTERDETAIL", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "22");
                messagetxt.append("从表中从表(BASE_MASTERDETAIL)导出完成,共" + basemasterdetailmaps.size() + "条记录,正在导出项目资源树查询服务表(CFG_EXTTREE)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("从表中从表(BASE_MASTERDETAIL)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }

            //存放dtconfigid
            for (Map<String, Object> map : basemasterdetailmaps) {
                dtconfigidlist.add(map.get("DT_CONFIG_ID").toString());
            }
        }


        //5、导出资源树查询服务表-CFG_EXTTREE
        fields = getTableField("CFG_EXTTREE");
        sqltxt = "select " + fields + " from CFG_EXTTREE where GUID in(";
        sqltxt += getServiceID(serviceKeyValue, "treeconfigServer");
        List<Map<String, Object>> cfgexttreemaps = columnMapper.executequerySql(sqltxt);
        if (cfgexttreemaps != null && cfgexttreemaps.size() > 0) {
            boolean result = exportDocument(cfgexttreemaps, "CFG_EXTTREE", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "27");
                messagetxt.append("资源树查询服务表(CFG_EXTTREE)导出完成,共" + cfgexttreemaps.size() + "条记录,正在导出项目资源树层级配置表(BASE_TREECONFIG)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("资源树查询服务表(CFG_EXTTREE)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }

            //存放dbkey
            for (Map<String, Object> map : cfgexttreemaps) {
                dbkeylist.add(map.get("DT_DB_KEY").toString());
            }
        }

        //5-1、导出资源树层级配置表-BASE_TREECONFIG
        fields = getTableField("BASE_TREECONFIG");
        sqltxt = "select " + fields + " from BASE_TREECONFIG where SERVICEID in(";
        sqltxt += getServiceID(serviceKeyValue, "treeconfigServer");
        List<Map<String, Object>> basetreeconfigmaps = columnMapper.executequerySql(sqltxt);
        if (basetreeconfigmaps != null && basetreeconfigmaps.size() > 0) {
            boolean result = exportDocument(basetreeconfigmaps, "BASE_TREECONFIG", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "31");
                messagetxt.append("资源树层级配置表(BASE_TREECONFIG)导出完成,共" + basetreeconfigmaps.size() + "条记录,正在导出项目脚本服务配置表(CFG_EXTSQL)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("资源树层级配置表(BASE_TREECONFIG)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }

            //存放dtconfig
            for (Map<String, Object> map : basetreeconfigmaps) {
                dtconfigidlist.add(map.get("DT_CONFIG_ID").toString());
            }
        }


        //6、导出脚本服务配置表-CFG_EXTSQL
        fields = getTableField("CFG_EXTSQL");
        sqltxt = "select " + fields + " from CFG_EXTSQL where GUID in(";
        sqltxt += getServiceID(serviceKeyValue, "executeSqlServer");
        List<Map<String, Object>> cfgextsqlmaps = columnMapper.executequerySql(sqltxt);
        if (cfgextsqlmaps != null && cfgextsqlmaps.size() > 0) {
            boolean result = exportDocument(cfgextsqlmaps, "CFG_EXTSQL", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "36");
                messagetxt.append("项目脚本服务配置表(CFG_EXTSQL)导出完成,共" + cfgextsqlmaps.size() + "条记录,正在导出项目脚本字段表(BASE_SQLCONFIG)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("项目脚本服务配置表(CFG_EXTSQL)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }

            //存放dbkey
            for (Map<String, Object> map : cfgextsqlmaps) {
                dbkeylist.add(map.get("DT_DB_KEY").toString());
            }

        }

        //6-1、导出脚本字段表-BASE_SQLCONFIG
        fields = getTableField("BASE_SQLCONFIG");
        sqltxt = "select " + fields + " from BASE_SQLCONFIG where SERVICEID in(";
        sqltxt += getServiceID(serviceKeyValue, "executeSqlServer");
        List<Map<String, Object>> basesqlconfigmaps = columnMapper.executequerySql(sqltxt);
        if (basesqlconfigmaps != null && basesqlconfigmaps.size() > 0) {
            boolean result = exportDocument(basesqlconfigmaps, "BASE_SQLCONFIG", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "40");
                messagetxt.append("脚本字段表(BASE_SQLCONFIG)导出完成,共" + basesqlconfigmaps.size() + "条记录,正在导出项目服务显示列表(BASE_SHOWFIELD)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("脚本字段表(BASE_SQLCONFIG)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }
        }

        //7、导出单表编辑服务基本信息表-CFG_EDIT
        fields = getTableField("CFG_EDIT");
        sqltxt = "select " + fields + " from CFG_EDIT where GUID in(";
        sqltxt += getServiceID(serviceKeyValue, "tableEditServer");
        List<Map<String, Object>> cfgeditmaps = columnMapper.executequerySql(sqltxt);
        if (cfgeditmaps != null && cfgeditmaps.size() > 0) {
            boolean result = exportDocument(cfgeditmaps, "CFG_EDIT", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "45");
                messagetxt.append("项目单表编辑服务基本信息表(CFG_EDIT)导出完成,共" + cfgeditmaps.size() + "条记录,正在导出项目单表编辑服务配置表(BASE_EDITCONFIG)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("项目单表编辑服务基本信息表(CFG_EDIT)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }

            for (Map<String, Object> map : cfgeditmaps) {
                //存放dbkey
                dbkeylist.add(map.get("DB_KEY").toString());
                //存放dtconfig
                dtconfigidlist.add(map.get("DT_CONFIG_ID").toString());
            }
        }

        //7-1、导出单表编辑服务配置表-BASE_EDITCONFIG
        fields = getTableField("BASE_EDITCONFIG");
        sqltxt = "select " + fields + " from BASE_EDITCONFIG where SERVICEID in(";
        sqltxt += getServiceID(serviceKeyValue, "tableEditServer");
        List<Map<String, Object>> baseEditConfigMaps = columnMapper.executequerySql(sqltxt);
        if (baseEditConfigMaps != null && baseEditConfigMaps.size() > 0) {
            boolean result = exportDocument(baseEditConfigMaps, "BASE_EDITCONFIG", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "49");
                messagetxt.append("项目单表编辑服务配置表(BASE_EDITCONFIG)导出完成,共" + baseEditConfigMaps.size() + "条记录,正在导出验证规则表(SYS_VALIDATION)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("项目单表编辑服务配置表(BASE_EDITCONFIG)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }
        }

		/*//7-2、导出验证规则表-SYS_VALIDATION
		fields = getTableField("SYS_VALIDATION");
		sqltxt = "select "+fields+" from SYS_VALIDATION ";
		List<Map<String,Object>> sysvalidationmaps = columnMapper.executequerySql(sqltxt);
		if(sysvalidationmaps != null && sysPrjservicemaps.size() > 0){
			boolean result = exportDocument(sysvalidationmaps,"SYS_VALIDATION",fields,url,fileName);
			if(result){
				progressSingleton.put("ratio","52");
				messagetxt.append("验证规则表(SYS_VALIDATION)导出完成,共"+sysvalidationmaps.size()+"条记录,正在导出全文检索基本信息表(CFG_EXTSEARCH)....<br/>");
				progressSingleton.put("message",messagetxt.toString());
			}else{
				messagetxt.append("验证规则表(SYS_VALIDATION)导出失败<br/>");
				progressSingleton.put("message", messagetxt.toString());
			}
		}*/

        //8、导出全文检索基本信息表-CFG_EXTSEARCH
        fields = getTableField("CFG_EXTSEARCH");
        sqltxt = "select " + fields + " from CFG_EXTSEARCH where GUID in(";
        sqltxt += getServiceID(serviceKeyValue, "searchServer");
        List<Map<String, Object>> cfgExtsearchmpas = columnMapper.executequerySql(sqltxt);
        if (cfgExtsearchmpas != null && cfgExtsearchmpas.size() > 0) {
            boolean result = exportDocument(cfgExtsearchmpas, "CFG_EXTSEARCH", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "55");
                messagetxt.append("全文检索基本信息表(CFG_EXTSEARCH)导出完成,共" + cfgExtsearchmpas.size() + "条记录,正在导出全文检索服务配置表(BASE_SEARCH)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("全文检索基本信息表(CFG_EXTSEARCH)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }
        }

        //8-1、导出全文检索服务配置表(BASE_SEARCH)
        fields = getTableField("BASE_SEARCH");
        sqltxt = "select " + fields + " from BASE_SEARCH where SERVICEID in(";
        sqltxt += getServiceID(serviceKeyValue, "searchServer");
        List<Map<String, Object>> baseSearchmaps = columnMapper.executequerySql(sqltxt);
        if (baseSearchmaps != null && baseSearchmaps.size() > 0) {
            boolean result = exportDocument(baseSearchmaps, "BASE_EDITCONFIG", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "57");
                messagetxt.append("全文检索服务配置表(BASE_SEARCH)导出完成,共" + baseSearchmaps.size() + "条记录,正在导出服务显示列表(BASE_SHOWFIELD)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("全文检索服务配置表(BASE_SEARCH)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }

            for (Map<String, Object> map : baseSearchmaps) {
                //存放dbkey
                dbkeylist.add(map.get("DB_KEY").toString());
                //存放dtconfig
                dtconfigidlist.add(map.get("DT_CONFIG_ID").toString());
            }

        }


        //9、导出服务显示列表-BASE_SHOWFIELD
        fields = getTableField("BASE_SHOWFIELD");
        sqltxt = "select " + fields + " from BASE_SHOWFIELD where SERVICEID in(";
        if (serviceKeyValue != null && serviceKeyValue.size() > 0) {
            String keyvalue = "";
            for (KeyValueModelVo kv : serviceKeyValue) {
                if (keyvalue == null || keyvalue.equals("")) {
                    keyvalue += "'" + kv.getKey() + "'";
                } else {
                    keyvalue += ",'" + kv.getKey() + "'";
                }
            }
            if (keyvalue == null || keyvalue.equals("")) {
                keyvalue = "''";
            }
            sqltxt += keyvalue;
        } else {
            sqltxt += "''";
        }
        sqltxt += ")";
        List<Map<String, Object>> baseshowfieldmaps = columnMapper.executequerySql(sqltxt);
        if (baseshowfieldmaps != null && baseshowfieldmaps.size() > 0) {
            boolean result = exportDocument(baseshowfieldmaps, "BASE_SHOWFIELD", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "60");
                messagetxt.append("服务显示列表(BASE_SHOWFIELD)导出完成,共" + baseshowfieldmaps.size() + "条记录,正在导出项目服务查询列表(BASE_QUERYFIELD)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("服务显示列表(BASE_SHOWFIELD)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }
        }


        //10、导出服务查询列表-BASE_QUERYFIELD
        fields = getTableField("BASE_QUERYFIELD");
        sqltxt = "select " + fields + " from BASE_QUERYFIELD where SERVICEID in(";
        if (serviceKeyValue != null && serviceKeyValue.size() > 0) {
            String keyvalue = "";
            for (KeyValueModelVo kv : serviceKeyValue) {
                if (keyvalue == null || keyvalue.equals("")) {
                    keyvalue += "'" + kv.getKey() + "'";
                } else {
                    keyvalue += ",'" + kv.getKey() + "'";
                }
            }
            if (keyvalue == null || keyvalue.equals("")) {
                keyvalue = "''";

            }
            sqltxt += keyvalue;
        } else {
            sqltxt += "''";
        }
        sqltxt += ")";
        List<Map<String, Object>> basequeryfieldmaps = columnMapper.executequerySql(sqltxt);
        if (basequeryfieldmaps != null && basequeryfieldmaps.size() > 0) {
            boolean result = exportDocument(basequeryfieldmaps, "BASE_QUERYFIELD", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "63");
                messagetxt.append("服务查询列表(BASE_QUERYFIELD)导出完成,共" + basequeryfieldmaps.size() + "条记录,正在导出项目服务查询列表(BASE_QUERYFIELD)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("服务查询列表(BASE_QUERYFIELD)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }
        }

        //10-1、导出查询列sql文表-BASE_QUERYSQL
        fields = getTableField("BASE_QUERYSQL");
        sqltxt = "select " + fields + " from BASE_QUERYSQL where SERVICEID in(";
        if (serviceKeyValue != null && serviceKeyValue.size() > 0) {
            String keyvalue = "";
            for (KeyValueModelVo kv : serviceKeyValue) {
                if (keyvalue == null || keyvalue.equals("")) {
                    keyvalue += "'" + kv.getKey() + "'";
                } else {
                    keyvalue += ",'" + kv.getKey() + "'";
                }
            }
            if (keyvalue == null || keyvalue.equals("")) {
                keyvalue = "''";
            }
            sqltxt += keyvalue;
        } else {
            sqltxt += "''";
        }
        sqltxt += ")";
        List<Map<String, Object>> basequerysqlmaps = columnMapper.executequerySql(sqltxt);
        if (basequerysqlmaps != null && basequerysqlmaps.size() > 0) {
            boolean result = exportDocument(basequerysqlmaps, "BASE_QUERYSQL", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "67");
                messagetxt.append("服务查询列表(BASE_QUERYFIELD)导出完成,共" + basequerysqlmaps.size() + "条记录,正在导出项目数据源表(CFG_DB)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("服务查询列表(BASE_QUERYFIELD)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }
        }

        //11、导出数据源表-CFG_DB
        fields = getTableField("CFG_DB");
        sqltxt = "select " + fields + " from CFG_DB where DB_KEY in(";
        if (dbkeylist != null && dbkeylist.size() > 0) {
            String strdbkey = "";
            for (String str : dbkeylist) {
                if (strdbkey == null || strdbkey.equals("")) {
                    strdbkey += "'" + str + "'";
                } else {
                    strdbkey += ",'" + str + "'";
                }
            }
            if (strdbkey == null || strdbkey.equals("")) {
                strdbkey = "''";
            }
            sqltxt += strdbkey;
        } else {
            sqltxt += "''";
        }
        sqltxt += ")";
        List<Map<String, Object>> cfgdbmaps = columnMapper.executequerySql(sqltxt);
        if (cfgdbmaps != null && cfgdbmaps.size() > 0) {
            boolean result = exportDocument(cfgdbmaps, "CFG_DB", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "72");
                messagetxt.append("数据源表(CFG_DB)导出完成,共" + cfgdbmaps.size() + "条记录,正在导出项目数据库表(CFG_TABLE)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("数据源表(CFG_DB)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }
        }


        //12、导出数据库表-CFG_TABLE
        fields = getTableField("CFG_TABLE");
        sqltxt = "select " + fields + " from CFG_TABLE where DT_CONN in(";
        if (dbkeylist != null && dbkeylist.size() > 0) {
            String strdbkey = "";
            for (String str : dbkeylist) {
                if (strdbkey == null || strdbkey.equals("")) {
                    strdbkey += "'" + str + "'";
                } else {
                    strdbkey += ",'" + str + "'";
                }
            }
            if (strdbkey == null || strdbkey.equals("")) {
                strdbkey = "''";
            }
            sqltxt += strdbkey;
        } else {
            sqltxt += "''";
        }
        sqltxt += ")";
        if (dtconfigidlist != null && dtconfigidlist.size() > 0) {
            sqltxt += " and DT_CONFIG_ID in(";
            String strdtconfig = "";
            for (String str : dtconfigidlist) {
                if (strdtconfig == null || strdtconfig.equals("")) {
                    strdtconfig += "'" + str + "'";
                } else {
                    strdtconfig += ",'" + str + "'";
                }
            }
            sqltxt += strdtconfig + ")";
        }

        List<Map<String, Object>> cfgtablemaps = columnMapper.executequerySql(sqltxt);
        if (cfgtablemaps != null && cfgtablemaps.size() > 0) {
            boolean result = exportDocument(cfgtablemaps, "CFG_TABLE", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "76");
                messagetxt.append("数据库表(CFG_TABLE)导出完成,共" + cfgtablemaps.size() + "条记录,正在导出项目字段表(CFG_FIELD)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("数据库表(CFG_TABLE)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }
        }

        //13、导出字段表-CFG_FIELD
        fields = getTableField("CFG_FIELD");
        sqltxt = "select " + fields + " from CFG_FIELD where DT_CONFIG_ID in(";
        if (dtconfigidlist != null && dtconfigidlist.size() > 0) {
            String strdtconfig = "";
            for (String str : dtconfigidlist) {
                if (strdtconfig == null || strdtconfig.equals("")) {
                    strdtconfig += "'" + str + "'";
                } else {
                    strdtconfig += ",'" + str + "'";
                }
            }
            if (strdtconfig == null || strdtconfig.equals("")) {
                strdtconfig = "''";
            }
            sqltxt += strdtconfig;
        } else {
            sqltxt += "''";
        }
        sqltxt += ")";
        List<Map<String, Object>> cfgfieldmaps = columnMapper.executequerySql(sqltxt);
        if (cfgfieldmaps != null && cfgfieldmaps.size() > 0) {
            boolean result = exportDocument(cfgfieldmaps, "CFG_FIELD", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "81");
                messagetxt.append("字段表(CFG_FIELD)导出完成,共" + cfgfieldmaps.size() + "条记录,正在导出项目字段枚举表(BASE_FIELDENUM)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("字段表(CFG_FIELD)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }
        }

        //14、导出字段枚举表-BASE_FIELDENUM
        fields = getTableField("BASE_FIELDENUM");
        sqltxt = "select " + fields + " from BASE_FIELDENUM where DT_CONFIG_ID in(";
        if (dtconfigidlist != null && dtconfigidlist.size() > 0) {
            String strdtconfig = "";
            for (String str : dtconfigidlist) {
                if (strdtconfig == null || strdtconfig.equals("")) {
                    strdtconfig += "'" + str + "'";
                } else {
                    strdtconfig += ",'" + str + "'";
                }
            }
            if (strdtconfig == null || strdtconfig.equals("")) {
                strdtconfig = "''";
            }
            sqltxt += strdtconfig;
        } else {
            sqltxt += "''";
        }
        sqltxt += ")";
        List<Map<String, Object>> basefieldnummaps = columnMapper.executequerySql(sqltxt);
        if (basefieldnummaps != null && basefieldnummaps.size() > 0) {
            boolean result = exportDocument(basefieldnummaps, "BASE_FIELDENUM", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "85");
                messagetxt.append("字段枚举表(BASE_FIELDENUM)导出完成,共" + basefieldnummaps.size() + "条记录,正在导出项目视图关联表(BASE_FIELDCONFIG)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("字段枚举表(BASE_FIELDENUM)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }
        }


        //15、导出视图关联表-BASE_FIELDCONFIG
        fields = getTableField("BASE_FIELDCONFIG");
        sqltxt = "select " + fields + " from BASE_FIELDCONFIG where DT_CONFIG_ID in(";
        if (dtconfigidlist != null && dtconfigidlist.size() > 0) {
            String strdtconfig = "";
            for (String str : dtconfigidlist) {
                if (strdtconfig == null || strdtconfig.equals("")) {
                    strdtconfig += "'" + str + "'";
                } else {
                    strdtconfig += ",'" + str + "'";
                }
            }
            if (strdtconfig == null || strdtconfig.equals("")) {
                strdtconfig = "''";
            }
            sqltxt += strdtconfig;
        } else {
            sqltxt += "''";
        }
        sqltxt += ")";
        List<Map<String, Object>> basefieldconfigmaps = columnMapper.executequerySql(sqltxt);
        if (basefieldconfigmaps != null && basefieldconfigmaps.size() > 0) {
            boolean result = exportDocument(basefieldconfigmaps, "BASE_FIELDCONFIG", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "90");
                messagetxt.append("视图关联表(BASE_FIELDCONFIG)导出完成,共" + basefieldconfigmaps.size() + "条记录,正在导出项目视图字段表(BASE_FIELDADDONS)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("视图关联表(BASE_FIELDCONFIG)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }

        }


        //16、导出视图字段表-BASE_FIELDADDONS
        fields = getTableField("BASE_FIELDADDONS");
        sqltxt = "select " + fields + " from BASE_FIELDADDONS where DT_CONFIG_ID in(";
        if (dtconfigidlist != null && dtconfigidlist.size() > 0) {
            String strdtconfig = "";
            for (String str : dtconfigidlist) {
                if (strdtconfig == null || strdtconfig.equals("")) {
                    strdtconfig += "'" + str + "'";
                } else {
                    strdtconfig += ",'" + str + "'";
                }
            }
            if (strdtconfig == null || strdtconfig.equals("")) {
                strdtconfig = "''";
            }
            sqltxt += strdtconfig;
        } else {
            sqltxt += "''";
        }
        sqltxt += ")";
        List<Map<String, Object>> basefieldaddonsmaps = columnMapper.executequerySql(sqltxt);
        if (basefieldaddonsmaps != null && basefieldaddonsmaps.size() > 0) {
            boolean result = exportDocument(basefieldaddonsmaps, "BASE_FIELDADDONS", fields, url, fileName);
            if (result) {
                progressSingleton.put("ratio", "94");
                messagetxt.append("视图字段表(BASE_FIELDADDONS)导出完成,共" + basefieldaddonsmaps.size() + "条记录,正在导出项目基本服务表(BASE_SERVICES)....<br/>");
                progressSingleton.put("message", messagetxt.toString());
            } else {
                messagetxt.append("视图字段表(BASE_FIELDADDONS)导出失败<br/>");
                progressSingleton.put("message", messagetxt.toString());
            }
        }
		
		/*//17、导出基本服务表-BASE_SERVICES
		fields = getTableField("BASE_SERVICES");
		sqltxt = "select "+fields+" from BASE_SERVICES ";
		List<Map<String,Object>> baseservicesmaps = columnMapper.executequerySql(sqltxt);
		if(baseservicesmaps != null && baseservicesmaps.size() > 0){
			boolean result = exportDocument(baseservicesmaps,"BASE_SERVICES",fields,url,fileName);
			if(result){
				progressSingleton.put("ratio","99");
				messagetxt.append("基本服务表(BASE_SERVICES)导出完成,共"+baseservicesmaps.size()+"条记录...<br/>");
				progressSingleton.put("message",messagetxt.toString());
			}else{
				messagetxt.append("基本服务表(BASE_SERVICES)导出失败<br/>");
				progressSingleton.put("message", messagetxt.toString());
			}
		}*/


        return fileName;

    }


    /**
     * show 通过表名称查询字段列
     *
     * @param tableName(表名称)
     * @return 字段code串, 已','隔开
     * @author guoyanfei
     * @date 2017/12/12
     */
    public String getTableField(String tableName) {
        StringBuilder fields = new StringBuilder();
        String databaseType = dataBaseConnInfo.dataBaseType;
        List<KeyValueModelVo> fieldlist = null;
        List<KeyValueModelVoSqlite> fieldlistSQlite = null;
        if (databaseType.equals("oracle")) {
            fieldlist = columnMapper.getFieldCodeBytableNameOracle(tableName);
        } else if (databaseType.equals("mysql")) {
            fieldlist = columnMapper.getFieldCodeBytableNameMysql(tableName);
        } else if (databaseType.equals("sqlite")) {
            fieldlistSQlite = columnMapper.getFieldCodeBytableNameSqlite(tableName);
        } else if (databaseType.equals(DataSourceType.POSTGRES)) {
            fieldlist = columnMapper.getFieldCodeBytableNamePG(tableName);
            fieldlist = fieldlist.stream().filter(o -> !o.getKey().contains("........")).peek(o -> {
                String dataType = o.getDataType();
                dataType = dataType.contains("(") ? dataType.substring(0, dataType.indexOf("(")) : dataType;
                dataType = dataType.replaceAll("\\[|]|\"", "");
                switch (dataType) {
                    case "character varying":
                        dataType = "varchar";
                        break;
                    case "timestamp with time zone":
                        dataType = "timestamp";
                        break;
                    default:
                }
                o.setDataType(dataType);
            }).collect(Collectors.toList());
        }

        if (fieldlist != null && fieldlist.size() > 0) {
            for (KeyValueModelVo str : fieldlist) {
                if (fields.toString().equals("")) {
                    fields.append(str.getKey());
                } else {
                    fields.append(",").append(str.getKey());
                }
            }
        }
        if (fieldlistSQlite != null && fieldlistSQlite.size() > 0) {
            for (KeyValueModelVoSqlite str : fieldlistSQlite) {
                if (fields.toString().equals("")) {
                    fields.append(str.getKey());
                } else {
                    fields.append(",").append(str.getKey());
                }
            }
        }
        return fields.toString();
    }

    public String getServiceID(List<KeyValueModelVo> serviceKeyValue, String serviceType) {
        String sqltxt = "";
        if (serviceKeyValue != null && serviceKeyValue.size() > 0) {
            StringBuilder keyvalue = new StringBuilder();
            for (KeyValueModelVo kv : serviceKeyValue) {
                if (kv.getValue().equals(serviceType)) {
                    if (keyvalue.toString().equals("")) {
                        keyvalue.append("'").append(kv.getKey()).append("'");
                    } else {
                        keyvalue.append(",'").append(kv.getKey()).append("'");
                    }
                }
            }
            if (keyvalue.toString().equals("")) {
                keyvalue = new StringBuilder("''");
            }
            sqltxt += keyvalue;
        } else {
            sqltxt += "''";
        }
        sqltxt += ")";

        return sqltxt;
    }

    /**
     * show 写入文档
     *
     * @param maps(数据集合)
     * @param tableName(表名称)
     * @param fields(字段code串,已','隔开)
     * @param url(文件路径)
     * @param fileName(文件名称)
     * @return boolean类型(执行结果)
     * @author guoyanfei
     * @date 2017/12/12
     */
    public boolean exportDocument(List<Map<String, Object>> maps, String tableName, String fields, String url, String fileName) {
        try {
            //判断文件目录是否存在
            File rootDir = new File(url);
            if (!rootDir.exists()) {
                rootDir.mkdirs();
            }

            //判断文件是否存在
            File file = new File(url + "/" + fileName);
            if (!file.isDirectory()) {
                file.createNewFile();
            }

            BufferedWriter bWriter = new BufferedWriter(new FileWriter(url + "/" + fileName, true));
            String databaseType = dataBaseConnInfo.dataBaseType;
            List<KeyValueModelVo> fieldlist = null;
            if (databaseType.equals("oracle")) {
                fieldlist = columnMapper.getFieldCodeBytableNameOracle(tableName);
            } else if (databaseType.equals("mysql") || databaseType.equals("sqlite")) {
                fieldlist = columnMapper.getFieldCodeBytableNameMysql(tableName);
            } else if (databaseType.equals(DataSourceType.POSTGRES)) {
                fieldlist = columnMapper.getFieldCodeBytableNamePG(tableName);
                fieldlist = fieldlist.stream().filter(o -> !o.getKey().contains("........")).peek(o -> {
                    String dataType = o.getDataType();
                    dataType = dataType.contains("(") ? dataType.substring(0, dataType.indexOf("(")) : dataType;
                    dataType = dataType.replaceAll("\\[|]|\"", "");
                    switch (dataType) {
                        case "character varying":
                            dataType = "varchar";
                            break;
                        case "timestamp with time zone":
                            dataType = "timestamp";
                            break;
                        default:
                    }
                    o.setDataType(dataType);
                }).collect(Collectors.toList());
            }
            if (fieldlist == null) {
                return false;
            }
            for (Map<String, Object> map : maps) {
                String sqltxt = "insert into " + tableName + "(" + fields + ") values(";
                StringBuilder sqlvalue = new StringBuilder();
                for (KeyValueModelVo str : fieldlist) {
                    String datavalue = "";
                    //数据为null，直接赋值null
                    if (map.get(str.getKey()) == null) {
                        datavalue = "null";
                    } else {
                        //数据类型为DATE，格式转换
                        if (databaseType.equals("oracle")) {
                            if (str.getDataType().equals("DATE")) {
                                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                String dateString = formatter.format(map.get(str.getKey()));
                                datavalue = "to_date('" + dateString + "', 'yyyy-mm-dd hh24:mi:ss')";
                            } else {
                                //数据类型为其他，直接赋值
                                datavalue = "'" + map.get(str.getKey()).toString().replace("'", "''") + "'";
                            }
//                        } else if (databaseType.equals("mysql")) {
                        } else {
                            datavalue = "'" + map.get(str.getKey()).toString().replace("'", "''") + "'";
                        }
                    }

                    if (sqlvalue.toString().equals("")) {
                        sqlvalue.append(datavalue);
                    } else {
                        sqlvalue.append(",").append(datavalue);
                    }
                }
                sqltxt += sqlvalue + ");";

                //写入文件
                bWriter.write(sqltxt.toString());
                bWriter.newLine();
                bWriter.flush();
            }
            bWriter.close();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public String importData(InputStream inputStream, ProgressSingleton progressSingleton) throws SQLException {
        java.sql.Connection conn = null;
        StringBuilder messagetxt = new StringBuilder();
        try {
            Class.forName(environment.getProperty("spring.db.driver-class-name"));
        } catch (ClassNotFoundException e) {
            messagetxt.append("注册驱动失败 ");
            messagetxt.append(e.getMessage());
            progressSingleton.put("message", messagetxt.toString());
        }
        String sqlUrl = environment.getProperty("spring.db.url");
        String user = environment.getProperty("spring.db.username");
        String password = environment.getProperty("spring.db.password");
        try {
            conn = java.sql.DriverManager.getConnection(sqlUrl, user, password);
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            messagetxt.append("连接数据库失败 ");
            messagetxt.append(e.getMessage());
            progressSingleton.put("message", messagetxt.toString());
        }
        //FileSystemResource read = new FileSystemResource(url);
        InputStreamResource read = new InputStreamResource(inputStream);
        EncodedResource er = new EncodedResource(read, "UTF-8");
        try {
            ScriptUtils.executeSqlScript(conn, er);
            conn.commit();
            messagetxt.append("sql导入成功 ");
            progressSingleton.put("message", messagetxt.toString());

        } catch (Exception e) {
            messagetxt.append("sql导入失败 ");
            conn.rollback();
            messagetxt.append(e.getMessage());
            progressSingleton.put("message", messagetxt.toString());
        }
        return messagetxt.toString();
    }
}
